/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package	dao;

import Hibernate.HibernateUtil;
import pojo.Author;
import pojo.Book;
import pojo.Publisher;
import pojo.Topic;
import pojo.Translator;


import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import org.hibernate.Hibernate;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;

public class BookDAO extends AbstractDAO {

	@Override
	protected Class getPOJOClass() {
		// TODO Auto-generated method stub
		return Book.class;
	}
	
    public Book getBookByID(int id){
        Book book = null;
        Session session = HibernateUtil.getSessionFactory().openSession();
        try{
            book = (Book)session.get(Book.class, id);
        }
        catch(HibernateException ex){
            
        }
        finally{
            session.close();
        }
        return book;
    }
    
    public int insert(String name, int authorID, int translatorID, int publisherID, int pageNum, float width, float height, float weight, double price, Date publishDay, String content){
    	Book book = new Book();
    	AuthorDAO authordao = new AuthorDAO();
    	TranslatorDAO translatordao = new TranslatorDAO();
    	PublisherDAO publisherdao = new PublisherDAO();
    	book.setName(name);
    	book.setAuthor(authordao.getAuthorByID(authorID));
    	book.setTranslator(translatordao.getTranslatorByID(translatorID));
    	book.setPublisher(publisherdao.getPublisherByID(publisherID));
    	book.setPageNum(pageNum);
    	book.setWidth(width);
    	book.setHeight(height);
    	book.setWeight(weight);
    	book.setPrice(price);
    	book.setPublishDay(publishDay);
    	book.setContent(content);
    	
    	Session session = HibernateUtil.getSessionFactory().openSession();
		Transaction transaction = null;
		try {
			transaction = session.beginTransaction();
			session.save(book);
			transaction.commit();
		} catch (HibernateException ex) {
			// Log the exception
			transaction.rollback();
		}
		finally {
			session.close();
		}
		
		return book.getBookId();
    }
    public Book returnBook(String name, Author author, Translator translator, Publisher publisher, int pageNum, float width, float height, float weight, double price, Date publishDay, String content){
    	Book book = new Book();
    	//AuthorDAO authordao = new AuthorDAO();
    	//TranslatorDAO translatordao = new TranslatorDAO();
    	//PublisherDAO publisherdao = new PublisherDAO();
    	book.setName(name);
    	book.setAuthor(author);
    	book.setTranslator(translator);
    	book.setPublisher(publisher);
    	book.setPageNum(pageNum);
    	book.setWidth(width);
    	book.setHeight(height);
    	book.setWeight(weight);
    	book.setPrice(price);
    	book.setPublishDay(publishDay);
    	book.setContent(content);    	
		return book;
    }
    
    
    public int insert(Book book)
    {
    	Session session = HibernateUtil.getSessionFactory().openSession();
		Transaction transaction = null;
		try {
			transaction = session.beginTransaction();
			session.save(book);
			int n=book.getBookId();
			book.setImageUrl(Integer.toString(n));
			session.update(book);
			transaction.commit();
		} catch (HibernateException ex) {
			// Log the exception
			System.out.println(ex);
			transaction.rollback();
		}
		finally {
			session.close();	
		}
		
		
		return book.getBookId();
    }
    
    public int delete(Book book)
    {
    	Session session = HibernateUtil.getSessionFactory().openSession();
    	Book b=this.getBookByID(book.getBookId());
		Transaction transaction = null;
		try {
			transaction = session.beginTransaction();
			session.delete(b);
			session.flush();
			transaction.commit();
		} catch (HibernateException ex) {
			// Log the exception
			System.out.println(ex);
			transaction.rollback();
		}
		finally {
			session.close();	
		}
		
		
		return book.getBookId();
    }
    
    public List<Book> selectByTopic(Topic t)
    {
    	List<Book> ds = null;
		Session session = HibernateUtil.getSessionFactory().openSession();
		
    	if(t.getParentId()!=0)
    	{
    		
    		String hql = "select book from Book  where book.bookId = n";
    		Query query = session.createQuery(hql).setParameter("n", t.getTopicId());
    		 ds = (List<Book>) query.list();
    		session.close();
    		return ds;
    	}
    	else
    	{
    		TopicDAO td= new TopicDAO();
    		List<Topic> l=td.getMenu2(t);
    		List<Book> lb;
    		for(Topic to: l)
    		{
    			String hql = "select book from Book  where book.bookId = n";
        		Query query = session.createQuery(hql).setParameter("n", t.getTopicId());
        		 lb = (List<Book>) query.list();
        		 ds.addAll(lb);
    		}
    		return ds;
    	}
    }
    
    
    
    public List<Book> getNbookTopView(int n)
    {
    	List<Book> ds=null;
		Session session = HibernateUtil.getSessionFactory().openSession();
		String hql = "select s from Book s order by s.view desc";
		Query query = session.createQuery(hql);
		query.setMaxResults(n);
		ds = (List<Book>) query.list();
		session.close();
		return ds;
    
    }
    public List<Book> getNewBook(int n)
    {
    	List<Book> ds=null;
		Session session = HibernateUtil.getSessionFactory().openSession();
		String hql = "select s from Book s order by s.publishDay desc";
		Query query = session.createQuery(hql);
		query.setMaxResults(n);
		ds = (List<Book>) query.list();
		session.close();
		return ds;
    
    }
    public void update(Book b)
    {
    	Session session = HibernateUtil.getSessionFactory().openSession();
		Transaction transaction = null;
		try {
			transaction = session.beginTransaction();
			session.update(b);
			session.flush();
			transaction.commit();
		} catch (HibernateException ex) {
			// Log the exception
			System.out.println(ex);
			transaction.rollback();
		}
		finally {
			session.close();	
		}
    }
    public long tinhSoTrangTheoTopicCap1(Topic t, int soSachTrenTrang)
    {
    	TopicDAO td= new TopicDAO();
		List<Topic> lt=td.getMenu2forCount(t);
		List<Book> ds= null;
		String hql = "select count(*) from Book s where ";
		Session session = HibernateUtil.getSessionFactory().openSession();
		for(int i=0;i< lt.size();i++)
		{
			hql += String.format("s.topic.topicId = %s",lt.get(i).getTopicId());
			if(i<lt.size()-1) hql+=" or ";
		}
    	
    	long soTrang = 0;
		long soLuong = 0;

    	Query query = session.createQuery(hql);
    	Object obj = query.uniqueResult();
    	soLuong = (Long) obj;
    	if(soSachTrenTrang == -1)
    			soTrang = 1;
    	else{
    			soTrang = soLuong / soSachTrenTrang;
    			if(soLuong % soSachTrenTrang != 0)
    				soTrang++;
    		}
    	session.close();
		return soTrang;
    }
    
    public List<Book> getBookByTopic(Topic tp, int trang, int btt, int kieuSort)
    {
    	TopicDAO td= new TopicDAO();
		List<Topic> lt=td.getMenu2forCount(tp);
		List<Book> ds= null;
		String hql = "select s from Book s where  ";
		Session session = HibernateUtil.getSessionFactory().openSession();
		for(int i=0;i< lt.size();i++)
		{
			hql += String.format("s.topic.topicId = %s ",lt.get(i).getTopicId());
			if(i<lt.size()-1) hql+=" or ";
		}
		switch(kieuSort)
		{
			case 1:
				hql+=" order by s.name asc";
				break;
			case 2:
				hql+=" order by s.name desc";
				break;
			case 3:
				hql+=" order by s.price asc";
				break;
			case 4:
				hql+=" order by s.price desc";
				break;
			case 5:
				hql+=" order by s.publishDay asc";
				break;
			case 6:
				hql+=" order by s.publishDay desc";
				break;	
			
				
		}
		Query query = session.createQuery(hql);
		query.setFirstResult((trang - 1) * btt);
		query.setMaxResults(btt);
		ds= query.list();
		session.close();
		return ds;   	
    }
    
    public List<Author> getTopAuthorForTopic(Topic t)
    {
    	return null;
    }
    
    public List<String> getListString(String t)
    {
    	List<String> l=new ArrayList<String>();
    	int n1=0;
    	int n2=0;
    	n2=t.indexOf("+");
    	while(n2!=-1)
    	{
    		String t2=t.substring(n1, n2);
    		n1=n2+1;
    		n2=t.indexOf("+", n2+1);
    		l.add(t2);		
    	}
    	String t2= t.substring(n1,t.length());
    	l.add(t2);
    	return l;
    }
    public long tinhSoTrangTimKiemNhanh(Topic t, String tensach,int btt)
    {
		long soTrang = 0;
		long soLuong = 0;
		List<String> l=getListString(tensach);
		Session session = HibernateUtil.getSessionFactory().openSession();
		String hql = "select count(*) from Book s where " ;
		if(t!=null)
		{
			TopicDAO td= new TopicDAO();
			List<Topic> lt=td.getMenu2forCount(t);
			hql+=" ( ";
			for(int i=0;i< lt.size();i++)
			{
				hql += String.format("s.topic.topicId = %s",lt.get(i).getTopicId());
				if(i<lt.size()-1) hql+=" or ";
			}		
			hql+=" ) and ";
		}
		for(int i=0;i<l.size();i++)
		{
			hql+=" s.name like '%" +l.get(i).trim()+ "%'";
			if(i<l.size()-1) hql+=" and ";
		}
		Query query = session.createQuery(hql);
		Object obj = query.uniqueResult();
		soLuong = (Long)obj;
		if(btt == -1)
			soTrang = 1;
		else{
			soTrang = soLuong / btt;
			if(soLuong % btt != 0)
				soTrang++;
		}
		session.close();
		return soTrang;
    }
    public long tinhKetquaTimKiemNhanh(Topic t, String tensach,int btt)
    {
		long soTrang = 0;
		long soLuong = 0;
		List<String> l=getListString(tensach);
		Session session = HibernateUtil.getSessionFactory().openSession();
		String hql = "select count(*) from Book s where " ;
		if(t!=null)
		{
			TopicDAO td= new TopicDAO();
			List<Topic> lt=td.getMenu2forCount(t);
			hql+=" ( ";
			for(int i=0;i< lt.size();i++)
			{
				hql += String.format("s.topic.topicId = %s",lt.get(i).getTopicId());
				if(i<lt.size()-1) hql+=" or ";
			}		
			hql+=" ) and ";
		}
		for(int i=0;i<l.size();i++)
		{
			hql+=" s.name like '%" +l.get(i).trim()+ "%'";
			if(i<l.size()-1) hql+=" and ";
		}
		Query query = session.createQuery(hql);
		Object obj = query.uniqueResult();
		soLuong = (Long)obj;
		return soLuong;
    }
    public List<Book> getBookTimKiemNhanh(Topic t,String tensach,int trang,int btt,int kieusort)
    {
    	List<Book> ds= null;
    	
		Session session = HibernateUtil.getSessionFactory().openSession();
		String hql = "select s from Book s where " ;
		if(t!=null)
		{
			TopicDAO td= new TopicDAO();
			List<Topic> lt=td.getMenu2forCount(t);
			hql+=" ( ";
			for(int i=0;i< lt.size();i++)
			{
				hql += String.format("s.topic.topicId = %s",lt.get(i).getTopicId());
				if(i<lt.size()-1) hql+=" or ";
			}		
			hql+=" ) and ";
		}
		List<String> l=getListString(tensach);
		for(int i=0;i<l.size();i++)
		{
			hql+=" s.name like '%" +l.get(i).trim()+ "%'";
			if(i<l.size()-1) hql+=" and ";
		}
		
		switch(kieusort)
		{
			case 1:
				hql+="order by s.name asc";
				break;
			case 2:
				hql+="order by s.name desc";
				break;
			case 3:
				hql+="order by s.price asc";
				break;
			case 4:
				hql+="order by s.price desc";
				break;
			case 5:
				hql+="order by s.publishDay asc";
				break;
			case 6:
				hql+="order by s.publishDay desc";
				break;	
				
				
		}
		Query query = session.createQuery(hql);
		query.setFirstResult((trang - 1) * btt);
		query.setMaxResults(btt);
		ds= query.list();
		session.close();
		return ds;   	
    }
    
    public long tinhSoTrangTimKiemNangCao(String ten,String content,Topic tp, String tacgia, String dichgia, String nxb, String _loainxb,int nam,int thang, int btt,int giathap, int giacao)
    {
    	long soTrang = 0;
		long soLuong = 0;
		TopicDAO td= new TopicDAO();
		Session session = HibernateUtil.getSessionFactory().openSession();
		String hql = "select count(*) from Book s where 1=1 ";
		if(!ten.trim().equals(""))
		{
			List<String> l=getListString(ten);
			for(int i=0;i<l.size();i++)
			{
				hql+="and s.name like '%" +l.get(i).trim()+ "%'";
			}
		}
		
		if(!content.trim().equals(""))
			hql += " and s.content like '%" + content.trim()+"%'";
		if(tp!=null)
		{
			List<Topic> lt=td.getMenu2forCount(tp);
			hql+=" and( ";
			for(int i=0;i< lt.size();i++)
			{
				hql += String.format("s.topic.topicId = %s",lt.get(i).getTopicId());
				if(i<lt.size()-1) hql+=" or ";
			}		
			hql+=" )";
		}
		if(!tacgia.trim().equals(""))
			hql += " and s.author.name like '%" + tacgia.trim()+"%'";
		if(!dichgia.trim().equals(""))
			hql += " and s.translator.name like '%" + dichgia.trim()+"%'";
		
		if(!nxb.trim().equals(""))
			hql += " and s.publisher.name like '%" + nxb.trim()+"%'";
		int n=Integer.parseInt(_loainxb);
		if(giathap!=-1)
		{
			hql += " and s.price > " +Integer.toString(giathap);
		}
		if(giacao!=-1)
		{
			hql += " and s.price < " +Integer.toString(giacao);
		}
		if(nam!=-1)
		{	String dau="";
			switch(n)
			{
				case 1:
					dau=" = ";
					break;
				case 2:
					dau=" < ";	
					break;
				case 3:
					dau=" > ";
					break;	
					
			}
			if(thang!=-1)
			{
				hql+=" and month(s.publishDay)"+dau+Integer.toString(thang);
			}
			
			hql+=" and year(s.publishDay)"+dau+":nam";
		}
		
		Query query ;
		if(nam!=-1) query= session.createQuery(hql).setParameter("nam",nam);
		else query= session.createQuery(hql);
		Object obj = query.uniqueResult();
		soLuong = (Long)obj;
		if(btt == -1)
			soTrang = 1;
		else{
			soTrang = soLuong / btt;
			if(soLuong % btt != 0)
				soTrang++;
		}
		session.close();
		return soTrang;
    }
    public long tinhKetQuaTimKiemNangCao(String ten,String content,Topic tp, String tacgia, String dichgia, String nxb, String _loainxb,int nam,int thang, int btt,int giathap, int giacao)
    {
    	long soTrang = 0;
		long soLuong = 0;
		TopicDAO td= new TopicDAO();
		Session session = HibernateUtil.getSessionFactory().openSession();
		String hql = "select count(*) from Book s where 1=1 ";
		if(!ten.trim().equals(""))
		{
			List<String> l=getListString(ten);
			for(int i=0;i<l.size();i++)
			{
				hql+="and s.name like '%" +l.get(i).trim()+ "%'";
			}
		}
		
		if(!content.trim().equals(""))
			hql += " and s.content like '%" + content.trim()+"%'";
		if(tp!=null)
		{
			List<Topic> lt=td.getMenu2forCount(tp);
			hql+=" and( ";
			for(int i=0;i< lt.size();i++)
			{
				hql += String.format("s.topic.topicId = %s",lt.get(i).getTopicId());
				if(i<lt.size()-1) hql+=" or ";
			}		
			hql+=" )";
		}
		if(!tacgia.trim().equals(""))
			hql += " and s.author.name like '%" + tacgia.trim()+"%'";
		if(!dichgia.trim().equals(""))
			hql += " and s.translator.name like '%" + dichgia.trim()+"%'";
		
		if(!nxb.trim().equals(""))
			hql += " and s.publisher.name like '%" + nxb.trim()+"%'";
		if(giathap!=-1)
		{
			hql += " and s.price >" +Integer.toString(giathap);
		}
		if(giacao!=-1)
		{
			hql += " and s.price <" +Integer.toString(giacao);
		}
		int n=Integer.parseInt(_loainxb);
		if(nam!=-1)
		{	String dau="";
			switch(n)
			{
				case 1:
					dau=" = ";
					break;
				case 2:
					dau=" < ";	
					break;
				case 3:
					dau=" > ";
					break;	
					
			}
			if(thang!=-1)
			{
				hql+=" and month(s.publishDay)"+dau+Integer.toString(thang);
			}
			
			hql+=" and year(s.publishDay)"+dau+":nam";
		}
		
		Query query ;
		if(nam!=-1) query= session.createQuery(hql).setParameter("nam",nam);
		else query= session.createQuery(hql);
		Object obj = query.uniqueResult();
		soLuong = (Long)obj;
	
		session.close();
		return soLuong;
    }
    public List<Book> getBookTimKiemNangCao(String ten,String content,Topic tp, String tacgia, String dichgia, String nxb, String _loainxb,int nam,int thang, int btt,int trang,int kieusort,int giathap, int giacao)
    {
    	List<Book> ds= null;
    	TopicDAO td= new TopicDAO();
		Session session = HibernateUtil.getSessionFactory().openSession();
		String hql = "select s from Book s where 1=1";
		if(!ten.trim().equals(""))
		{
			List<String> l=getListString(ten);
			for(int i=0;i<l.size();i++)
			{
				hql+=" and s.name like '%" +l.get(i).trim()+ "%'";
			}
		}
		

		if(!content.trim().equals(""))
			hql += " and s.content like '%" + content.trim()+"%'";

		if(tp!=null)
		{
			List<Topic> lt=td.getMenu2forCount(tp);
			hql+=" and( ";
			for(int i=0;i< lt.size();i++)
			{
				hql += String.format("s.topic.topicId = %s",lt.get(i).getTopicId());
				if(i<lt.size()-1) hql+=" or ";
			}		
			hql+=" )";
		}
		if(!tacgia.trim().equals(""))
			hql += " and s.author.name like '%" + tacgia.trim()+"%'";
		if(!dichgia.trim().equals(""))
			hql += " and s.translator.name like '%" + dichgia.trim()+"%'";
		
		if(!nxb.trim().equals(""))
			hql += " and s.publisher.name like '%" + nxb.trim()+"%'";
		if(giathap!=-1)
		{
			hql += " and s.price >" +Integer.toString(giathap);
		}
		if(giacao!=-1)
		{
			hql += " and s.price <" +Integer.toString(giacao);
		}
		int n=Integer.parseInt(_loainxb);
		if(nam!=-1)
		{	String dau="";
			switch(n)
			{
				case 1:
					dau=" = ";
					break;
				case 2:
					dau=" < ";	
					break;
				case 3:
					dau=" > ";
					break;	
					
			}
			if(thang!=-1)
			{
				hql+=" and month(s.publishDay)"+dau+Integer.toString(thang);
			}
			
			hql+=" and year(s.publishDay)"+dau+":nam ";
		}
		switch(kieusort)
		{
			case 1:
				hql+=" order by s.name asc";
				break;
			case 2:
				hql+=" order by s.name desc";
				break;
			case 3:
				hql+=" order by s.price asc";
				break;
			case 4:
				hql+=" order by s.price desc";
				break;
			case 5:
				hql+=" order by s.publishDay asc";
				break;
			case 6:
				hql+=" order by s.publishDay desc";
				break;	
				
				
		}
		Query query ;
		if(nam!=-1) query= session.createQuery(hql).setParameter("nam",nam);
		else query= session.createQuery(hql);
		query.setFirstResult((trang - 1) * btt);
		query.setMaxResults(btt);
		ds= query.list();
		session.close();
		return ds;   	
    }
    
    
    public long tinhSoTrangTheoTacGia(Author a,int btt)
    {
		long soTrang = 0;
		long soLuong = 0;
		Session session = HibernateUtil.getSessionFactory().openSession();
		String hql = "select count(*) from Book s where s.author.authorId =:id " ;

		Query query = session.createQuery(hql).setParameter("id", a.getAuthorId());
		Object obj = query.uniqueResult();
		soLuong = (Long)obj;
		if(btt == -1)
			soTrang = 1;
		else{
			soTrang = soLuong / btt;
			if(soLuong % btt != 0)
				soTrang++;
		}
		session.close();
		return soTrang;
    }
    
    public List<Book> getBookTheoTacGia(Author a,int trang,int btt,int kieusort)
    {
    	List<Book> ds= null;
    	Session session = HibernateUtil.getSessionFactory().openSession();
		String hql = "select s from Book s where s.author.authorId =:id " ;
    	
		switch(kieusort)
		{
			case 1:
				hql+="order by s.name asc";
				break;
			case 2:
				hql+="order by s.name desc";
				break;
			case 3:
				hql+="order by s.price asc";
				break;
			case 4:
				hql+="order by s.price desc";
				break;
			case 5:
				hql+="order by s.publishDay asc";
				break;
			case 6:
				hql+="order by s.publishDay desc";
				break;	
				
				
		}
		Query query = session.createQuery(hql).setParameter("id", a.getAuthorId());
		query.setFirstResult((trang - 1) * btt);
		query.setMaxResults(btt);
		ds= query.list();
		session.close();
		return ds;   	
    }
    
    public long tinhSoTrangTheoDichGia(Translator t,int btt)
    {
		long soTrang = 0;
		long soLuong = 0;
		Session session = HibernateUtil.getSessionFactory().openSession();
		String hql = "select count(*) from Book s where s.translator.translatorId =:id " ;

		Query query = session.createQuery(hql).setParameter("id",t.getTranslatorId());
		Object obj = query.uniqueResult();
		soLuong = (Long)obj;
		if(btt == -1)
			soTrang = 1;
		else{
			soTrang = soLuong / btt;
			if(soLuong % btt != 0)
				soTrang++;
		}
		session.close();
		return soTrang;
    }
    
    public List<Book> getBookTheoDichGia(Translator t,int trang,int btt,int kieusort)
    {
    	List<Book> ds= null;
    	Session session = HibernateUtil.getSessionFactory().openSession();
		String hql = "select s from Book s where s.translator.translatorId =:id " ;
    	
		switch(kieusort)
		{
			case 1:
				hql+="order by s.name asc";
				break;
			case 2:
				hql+="order by s.name desc";
				break;
			case 3:
				hql+="order by s.price asc";
				break;
			case 4:
				hql+="order by s.price desc";
				break;
			case 5:
				hql+="order by s.publishDay asc";
				break;
			case 6:
				hql+="order by s.publishDay desc";
				break;	
				
				
		}
		Query query = session.createQuery(hql).setParameter("id", t.getTranslatorId());
		query.setFirstResult((trang - 1) * btt);
		query.setMaxResults(btt);
		ds= query.list();
		session.close();
		return ds;   	
    }
}
